from app import db, app


# # 2. 一对一关系
class IdentityCard(db.Model):
    """
    CREATE TABLE identity_card (
        id INTEGER NOT NULL,
        number VARCHAR(18) NOT NULL,
        person_id INTEGER,
        PRIMARY KEY (id),
        UNIQUE (number),
        FOREIGN KEY(person_id) REFERENCES users (user_id)
        )
    """

    id = db.Column(db.Integer, primary_key=True)
    number = db.Column(db.String(18), unique=True, nullable=False)
    # 创建外键
    person_id = db.Column(
        db.Integer,
        db.ForeignKey("users.user_id", onupdate="CASCADE"),
    )  # table name is users not user
    # 定义关系属性，并以标量的形式加载记录


class Department(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
    # 定义一对多关系属性，并以列表的形式加载记录
    user = db.relationship("User", backref="departments", lazy=True, uselist=False)


class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)


# class item_user_association(db.Model):
#     __tablename__ = "association"
#     id = db.Column(db.Integer, primary_key=True)
#     user_id = db.Column(db.Integer, db.ForeignKey("users.user_id"))
#     item_id = db.Column(db.Integer, db.ForeignKey("item.id"))

# 另外一种方式，使用db.Table创建表格
# 参考原生SQLAlchemy文档
# https://docs.sqlalchemy.org/en/20/orm/quickstart.html
association_table = db.Table(
    "association",
    db.Column("id", db.Integer, primary_key=True),
    db.Column("user_id", db.Integer, db.ForeignKey("users.user_id")),
    db.Column("item_id", db.Integer, db.ForeignKey("item.id")),
)


class User(db.Model):
    """
    CREATE TABLE users (
        user_id INTEGER NOT NULL,
        username VARCHAR(80) NOT NULL,
        email VARCHAR(120) NOT NULL,
        PRIMARY KEY (user_id),
        UNIQUE (username),
        UNIQUE (email)
    )
    """

    __tablename__ = "users"
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    dept_id = db.Column(
        db.Integer,
        db.ForeignKey("department.id"),
    )
    # # 定义一对一关系属性，并以标量的形式加载记录
    identity = db.relationship("IdentityCard", backref="user", uselist=True)
    items = db.relationship("Item", secondary=association_table, backref="user")

    def __repr__(self):
        return f"User {self.username}|Email: {self.email}"


if __name__ == "__main__":
    with app.app_context():
        db.drop_all()
        db.create_all()
